﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;

namespace MISAPP.Classify.DataAccess
{
    public class DA_ARTM95_ABCDE:DA_Matrix
    {
        public DA_ARTM95_ABCDE()
        {
            tableName = "ARTM95_ABCDE";
        }

        public DataTable GetAC001Info(string p_accno)
        {
            p_accno = string.Format("{0}%", p_accno.ToUpper().Trim());
            string sql = "SELECT TOP 10 * FROM artm.dbo.AC001 WHERE ACC_NO LIKE @p_accno";
            FillTableSchema("AC001");
            FillFieldList(new string[] { p_accno }
                , new string[] { "@p_accno" }, "ACC_NO");
            AddParameter(sql,DBOpt.ARTM);
            DataTable dt = getData();
            if(dt.Rows.Count.Equals(0))
            {
                dt = GetAC001Info2(string.Empty, p_accno);
            }
            //return getData();
            return dt;
        }

        /// <summary>
        /// 給彈出WINDOWS 查詢會計科目用
        /// </summary>
        /// <param name="p_accNo"></param>
        /// <param name="p_accName"></param>
        /// <returns></returns>
        public DataTable GetAC001Info2(string p_accNo,string p_accName)
        {
            string v1,v2;
            v1 = string.Empty;
            v2 = string.Empty;

            FillTableSchema("AC001");

            if (!string.Empty.Equals(p_accNo))
            {
                p_accNo = string.Format("{0}%", p_accNo.Trim());
                v1 = " AND ACC_NO LIKE @p_accNo";
                FillFieldList(new string[] { p_accNo }
                    , new string[] { "@p_accNo" }, "ACC_NO");

            }

            if (!string.Empty.Equals(p_accName))
            {
                p_accName = string.Format("%{0}%", p_accName.Trim());
                v2 = " AND ACC_NAME LIKE @p_accName ";
                FillFieldList(new string[] { p_accName }
                    , new string[] { "@p_accName" }, "ACC_NAME");

            }

            string sql = string.Format("SELECT * FROM artm.dbo.AC001 WHERE 1=1 {0}{1} ORDER BY ACC_NO"
                ,v1,v2);

            AddParameter(sql,DBOpt.ARTM);
            return getData();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_accno1"></param>
        /// <param name="p_accno2"></param>
        /// <param name="p_option"></param>
        /// <param name="p_year"></param>
        /// <param name="p_showTotal">是否顯示總數</param>
        /// <returns></returns>
        public DataTable GetSummaryData(string p_accno1, string p_accno2
            , string p_option,string p_year,bool p_showTotal)
        {
            string sql,strAcc,strAcc2,strTotal,strBal,lastField;
            string[] cateogrys = { "'001'","'002'","'OBU'","'海外'","'TWD'", "'555'"};
            string[] categoryCriterias = getCategoryCriterias();
            strTotal = string.Empty;
            strAcc = getParaBetweenString(p_accno1, p_accno2, "A.acc_no", "acc_no");
            strAcc2 = strAcc;
            strBal = "bal_2";
            lastField = ",CONVERT(VARCHAR, CAST(SUM(T3)/SUM(T2) AS MONEY)) [累計]	";
            switch (p_option)
            {
                case "1":
                    sql = getSQLStatementWith123();
                    strAcc2 = "";                    
                    break;
                case "2":
                    sql = getSQLStatementWith45();
                    strAcc2 = "";
                    break;
                case "3":
                    sql = getSQLWMGStatement();
                    strAcc = groupString;
                    strBal = "";
                    break;
                default:
                    sql = getSQLStatement();
                    strBal = "bal";
                    strAcc2 = "";
                    lastField = string.Empty;
                    break;
            }
            FillTableSchema(tableName);
            GetCriteriaString();
            CriteriaString = CriteriaString.Replace(" big", " A.big");
            CriteriaString = CriteriaString.Replace(" small", " A.small");
            CriteriaString = CriteriaString.Replace(" branch", " A.branch");
            sql = string.Format(sql
                , strAcc
                , CriteriaString  + strAcc2
                , getBetweenDateString(p_year)
                );

            if (p_showTotal.Equals(true))
            {
                StringBuilder sb = new StringBuilder();
                if ((Convert.ToInt16(p_option) <= 2) ||
                    (Convert.ToInt16(p_option) == 4))
                {
                    for (int i = 0; i < cateogrys.Length; i++)
                    {
                        ArrayList al = new ArrayList() {
                        cateogrys[i]
                        , strAcc + CriteriaString  + strAcc2
                        , getBetweenDateString(p_year)
                        , categoryCriterias[i]
                        , strBal
                    };
                        sb.AppendFormat("#{0}", get001Statement(al.ToArray()));
                    }
                    sql = string.Format(getTotalStatement()
                        , lastField, sb.ToString().Substring(1).Replace("#"
                            , " UNION ALL" + System.Environment.NewLine)
                        , sql);
                }            
            }
            AddParameter(sql,DBOpt.ARTM);
            return getData();

        }

        private string[] getCategoryCriterias()
        {
            return new string[] { 
                ""
                ,@" AND (A.branch LIKE '0%' 
		                OR A.branch LIKE '1%'
		                OR A.branch LIKE '2%'
		                OR A.branch LIKE '3%'
		                OR A.branch LIKE '4%'
		                OR A.branch LIKE '50%'
		                OR A.branch LIKE '51%'
		                OR A.branch LIKE '52%'
		                OR A.branch LIKE '53%'
		                OR A.branch LIKE '54%'
		                OR A.branch LIKE '55%'
		                OR A.branch LIKE '57%'
		                OR A.branch LIKE '58%'
		                OR A.branch LIKE '59%'
		                OR A.branch LIKE '6%'
		                OR A.branch LIKE '7%'
		                OR A.branch LIKE '8%')"
                ," AND A.branch LIKE '56%'"    
                ," AND A.branch LIKE '9%'"
                ," AND A.CUR = 'TWD'"
                ," AND A.CUR = '555'"               
                };
        }

        public override DataSet GetCriteria()
        {
            /*
            string[] criterias = GetDistinctField();
            string sql = "SELECT DISTINCT {1} FROM {0} ORDER BY 1 DESC";
            DataSet ds = new DataSet();
            DataTable dt = null;

            foreach (string fld in criterias)
            {
                dt = getData(string.Format(sql, tableName, fld)
                                , fld,DBOpt.ARTM);
                ds.Tables.Add(dt);
            }
            return ds;
             */
            DataSet ds = new DataSet();
            ds.Tables.Add(getBranchData());
            ds.Tables.Add(getBigData());
            ds.Tables.Add(getSmallData());
            return ds;
        }

        public DataTable GetSummaryData(string[] p_accno,string p_year)
        {
            FillTableSchema(tableName);
            GetCriteriaString();
            string sql = string.Format(getSQLStatement()
               , ""
               , CriteriaString
               , getBetweenDateString(p_year)
               );
            AddParameter(sql,DBOpt.ARTM);
            return getData();
        }

        public DataTable GetSummaryData(string p_accno1, string p_accno2)
        {
            FillTableSchema(tableName);
            GetCriteriaString();
            string sql = string.Format(getSQLStatement()
                , getParaBetweenString(p_accno1, p_accno2, "A.acc_no", "acc_no")
                , CriteriaString
                );
            AddParameter(sql,DBOpt.ARTM);
            return getData();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="p_ym">YYYYMM</param>
        /// <returns></returns>
        public DataTable GetDebtSummaryData(DateTime p_ymd)
        {
            return onlyDate(p_ymd, getDebtStatement());
        }

        public DataTable GetDismantleData(DateTime p_ymd)
        {
            FillTableSchema(tableName);
            GetCriteriaString();
            string sql = string.Format(getDismantleStatement(), CriteriaString);
            FillFieldList(new string[] { p_ymd.ToShortDateString() },
                new string[] { "@ymd" }, "ymd");
            AddParameter(sql,DBOpt.ARTM);
            return getData(); 
        }

        /// <summary>
        /// CIB孳息資產
        /// </summary>
        /// <param name="p_ymd"></param>
        /// <returns></returns>
        public DataTable GetYieldData(DateTime p_ymd)
        {
            return onlyDate(p_ymd, getYieldStatement());
        }

        public DataTable GetFeeData(DateTime p_ymd)
        {
            return onlyDate(p_ymd, getFeeStatement());
        }

        public DataTable GetFeeDataDetail(DateTime p_ymd)
        {
            return onlyDate(p_ymd, getFeeDataDetailStatement());
        }

        /// <summary>
        /// FMG孳息資產
        /// </summary>
        public DataTable GetFMGYieldData(DateTime p_ymd)
        {
            return onlyDate(p_ymd, getFMGYieldStatement());
        }

        /// <summary>
        /// 債票券
        /// </summary>
        /// <returns></returns>
        public DataTable GetTicketData(DateTime p_ymd)
        {
            return onlyDate(p_ymd, getTicketStatement());
        }

        /// <summary>
        /// 存央等
        /// </summary>
        /// <param name="p_ymd"></param>
        /// <returns></returns>
        public DataTable GetSaveCenData(DateTime p_ymd)
        {
            return onlyDate(p_ymd, getSaveCenStatement());
        }

        /// <summary>
        /// 在excel中的年月要用YYYYMM 這樣的格式EX:201201
        /// </summary>
        /// <param name="p_ymd"></param>
        /// <returns></returns>
        public DataTable GetIncomeCostData(DateTime p_ymd)
        {
            string sql = string.Format(getIncomeCostStatement()
                            , p_ymd.Year.ToString()
                            , "('F','L','8')" );
            return getData(sql, "income",DBOpt.ARTM);
        }

        /* 2013-07-03 先註解，確定沒有使用再刪除
        public DataTable GetIncomeCostData8788(DateTime p_ymd)
        {
            string sql = string.Format(getIncomeCostStatement()
                            , p_ymd.Year.ToString()
                            , "('F','L','8')");
            return getData(sql, "income");
        }
        */

        public DataTable GetBusinessCostData(DateTime p_ymd)
        {
            string sql = string.Format(getBusinessCostStatement()
                    ,p_ymd.Year.ToString()
                    ,"A.acc_no LIKE '5811%' OR A.acc_no = '5832000000'"
                    ,"('8','F')"
                    ,"('6','2','7','8','B','C','P','Q','R','G','F','H','U')"
                );
            return getData(sql,"business",DBOpt.ARTM);
        }

        /// <summary>
        /// 傳入目前的時間，Year - 1 取得前一年
        /// </summary>
        /// <param name="p_ymd"></param>
        /// <returns></returns>
        public DataTable GetIncomeCost2YData(DateTime p_ymd)
        {
            string sql,y1,y2,m1;
            y1 = p_ymd.Year.ToString();
            y2 = Convert.ToString(p_ymd.Year-1);
            m1 = p_ymd.Month.ToString();
            sql = string.Format(getIncomeCostBetween2YStatement()
                , y1, y2, m1);
            return getData(sql, "TwoYear",DBOpt.ARTM);
        }
        /// <summary>
        /// 營業費用9大類
        /// </summary>
        /// <param name="p_dates"></param>
        /// <returns></returns>
        public DataTable GetOperatingExp(object[] p_dates)
        {
            //GetCriteriaString();
            /*
            parseParameter(p_dates.GetValue(2) as Dictionary<string, string>);
            string sql = string.Format(getOperatingExpStatement()
                , CriteriaString);
            object[] dates = { p_dates.GetValue(0), p_dates.GetValue(1) };
            FillFieldList(dates
                , new string[] { "@ymd1", "@ymd2" }, "ymd");
            AddParameter(sql);
            return getData();
             */
            string[] sql = { getOperatingExpStatement(),"" };
            return getOperatingExpResult(p_dates, sql);
        }

        public DataTable GetProfitAndLost(object[] p_dates)
        {
            string[] sql = { getProfitAndLostStatement(), "" };
            return getOperatingExpResult(p_dates, sql);        
        }

        public DataTable GetTMUData(object[] p_dates)
        {
            string sql = string.Format(getTMUStatement(), p_dates);
            return getData(sql, "tmu",DBOpt.ARTM);
        }

        /// <summary>
        /// 營業費用9大類中的明細
        /// </summary>
        /// <param name="p_dates"></param>
        /// <returns></returns>
        public DataTable GetOperatingExpDtl(object[] p_dates)
        {
            string[] sql = { getOperatingExpDetailStatement()
             , parseBigField(p_dates.GetValue(2) as Dictionary<string, string>) };
            return getOperatingExpResult(p_dates , sql);
        }

        /// <summary>
        /// 按大小版，column 依月份把值加出來
        /// </summary>
        /// <param name="p_dates"></param>
        /// <returns></returns>
        public DataTable GetOPeratingExpMonth(object[] p_dates)
        {
            string[] sql = { getOPerationExpByMonthStatement(), "" };
            return getOperatingExpResult(p_dates, sql);        
        }

        public DataTable GetPeratingExpMonthDetail(object[] p_dates)
        {
            string[] sql = { getOPerationExpByMonthDetailStatement(), "" };
            return getOperatingExpResult(p_dates, sql);        
        }        

        protected DataTable getOperatingExpResult(object[] p_dates,string[] p_sql)
        {
            parseParameter(p_dates.GetValue(2) as Dictionary<string, string>);
            string sql = string.Format(p_sql[0] , CriteriaString,p_sql[1]);
            object[] dates = { p_dates.GetValue(0), p_dates.GetValue(1) };
            FillFieldList(dates
                , new string[] { "@ymd1", "@ymd2" }, "ymd");
            AddParameter(sql,DBOpt.ARTM);
            return getData();
        }

        protected string parseBigField(Dictionary<string, string> p_dict)
        {
            string fld = ",SUM(CASE WHEN big = '{0}' THEN B.bal_2 ELSE 0 END)AMOUNT_{0}{1}";
            char separator1;
            separator1 = '|';
            string[] bgs = p_dict["big"].ToString().Split(separator1);
            StringBuilder sb = new StringBuilder();
            foreach (string bg in bgs)
            {
                sb.AppendFormat(fld, bg,Environment.NewLine);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 目前只有對big、small 這兩個來做
        /// </summary>
        /// <param name="p_dict"></param>
        protected void parseParameter(Dictionary<string,string>p_dict)
        {
            char separator1, separator2;
            separator1= '|';
            separator2 = '_';
            string[] bgs, sms,sms2,branchs;
            //(big = '8' AND small IN ('7','8'))
            string tmp,small,star,outTemp;
            star = "*";
            small = string.Empty;
            tmp = "#(big = '{0}' {1} )";
            //這兩個陣列應該是要成對！
            sms = null;
            CriteriaString = string.Empty;
            StringBuilder sbSmall = new StringBuilder();
            StringBuilder sb = new StringBuilder();
            //繞big
            if (p_dict.TryGetValue("big", out outTemp))
            {
                bgs = p_dict["big"].ToString().Split(separator1);
                if (p_dict.TryGetValue("small", out outTemp))
                {
                    sms = p_dict["small"].ToString().Split(separator1);
                }                                   
                for (int i = 0; i < bgs.Count(); i++)
                {
                    small = string.Empty;
                    if (sms != null)
                    {
                        sms2 = sms[i].Split(separator2);
                        for (int j = 0; j < sms2.Count(); j++)
                        {
                            if (!sms2[j].Equals(star))
                            {
                                sbSmall.AppendFormat(",'{0}'", sms2[j]);
                            }
                        }
                    }
                    if (sbSmall.Length > 0)
                    {
                        //AND small IN ('7','8')
                        small = string.Format(" AND small in({0})"
                            , sbSmall.ToString().Substring(1));
                        sbSmall.Remove(0, sbSmall.Length);
                    }
                    sb.AppendFormat(tmp, bgs[i], small);
                }
                CriteriaString = string.Format("AND ({0})"
                    , sb.ToString().Substring(1).Replace("#", " OR "));
            }
            //branch
            if (p_dict.TryGetValue("branch",out outTemp))
            {
                branchs = p_dict["branch"].ToString().Split(separator2);
                sbSmall = new StringBuilder();
                for (int i = 0; i < branchs.Count(); i++)
                {
                    sbSmall.AppendFormat(",'{0}'", branchs[i]);
                }
                CriteriaString = string.Format("{0} {1} AND BRANCH IN({2}) "
                , CriteriaString, Environment.NewLine
                , sbSmall.ToString().Substring(1));
            }
        }

        protected string parseDictionary(string p_key, Dictionary<string, string> p_dict)
        {
            string rtnValue,outValue;
            rtnValue = string.Empty;

            return rtnValue;
        }

        protected DataTable onlyDate(DateTime p_ymd,string p_sql)
        {
            FillTableSchema(tableName);
            FillFieldList(new string[] { p_ymd.ToShortDateString() },
                new string[] { "@ymd" }, "ymd");
            AddParameter(p_sql,DBOpt.ARTM);
            return getData(); 
        }

        protected string getParaBetweenString(string p_v1, string p_v2
            , string p_fldName,string p_atName)
        {
            string v1,at1,at2,fld;
            v1 = string.Empty;
            List<string> valueLst = null;
            List<string> paraList = null;
            at1 = string.Format("@{0}1", p_atName);
            at2 = string.Format("@{0}2", p_atName);
                
            if (!string.Empty.Equals(p_v1) & !string.Empty.Equals(p_v2))
            {
                if (p_v1.Length.Equals(10) & p_v2.Length.Equals(10))
                {
                    v1 = string.Format(" AND ({0} BETWEEN {1} AND {2})"
                        , p_fldName, at1, at2);
                    valueLst = new List<string>() { p_v1, p_v2 };
                    paraList = new List<string>() { at1, at2 };
                }
                else
                {
                    if (p_v1.Length.Equals(1) & p_v2.Length.Equals(1))
                    {
                        if (p_v1.Equals("1") & p_v2.Equals("5"))
                        {
                            v1 = string.Empty;
                        }
                        else
                        {
                            v1 = string.Format(" AND ( {0} LIKE {1} OR {0} LIKE {2} )"
                                , p_fldName, at1, at2);
                            valueLst = new List<string>() { string.Format("{0}%", p_v1)
                            , string.Format("{0}%", p_v2) };
                            paraList = new List<string>() { at1, at2 };  
                        }
                    }
                        /*
                    else
                    {
                        v1 = string.Format(" AND ( {0} LIKE {1} OR {0} LIKE {2} )"
                            , p_fldName, at1, at2);
                        valueLst = new List<string>() { string.Format("{0}%", p_v1)
                            , string.Format("{0}%", p_v2) };
                        paraList = new List<string>() { at1, at2 };                    
                    }
                         */

                }
            }
            if (!string.Empty.Equals(p_v1) & string.Empty.Equals(p_v2))
            {
                v1 = string.Format(" AND {0} LIKE {1}",p_fldName,at1);
                valueLst = new List<string>() { string.Format("{0}%",p_v1) };
                paraList = new List<string>() { at1 };
            }
            if (string.Empty.Equals(p_v1) & !string.Empty.Equals(p_v2))
            {
                v1 = string.Format(" AND {0} LIKE {1} ",p_fldName,at1);
                valueLst = new List<string>() { string.Format("{0}%",p_v2) };
                paraList = new List<string>() { at2 };
            }
            if (!string.Empty.Equals(v1))
            {
                fld = p_fldName.IndexOf('.') > 0 ?
                    p_fldName.Substring(p_fldName.IndexOf('.') + 1) : p_fldName;
                FillFieldList(valueLst.ToArray(), paraList.ToArray(), fld);
            }

            return v1;
        }

        protected string getParaString(string p_para,string p_fldName)
        {
            string v1 = "";
            if (!string.Empty.Equals(p_para))
            {
                v1 = string.Format(" AND {0}=@{0}",p_fldName);
                FillFieldList(new string[] { p_para }
                , new string[] { string.Format("@{0}",p_fldName) }, p_fldName);
            }
            return v1;
        }

        protected string getAccno(string p_accno1,string p_accno2)
        {
            string v1 = string.Empty;
            List<string> valueLst = null;
            List<string> paraList = null;
            if (!string.Empty.Equals(p_accno1) & !string.Empty.Equals(p_accno2))
            {
                v1 = " AND (A.acc_no BETWEEN @p_accno1 AND @p_accno2)";
                valueLst = new List<string>(){ p_accno1,p_accno2 };
                paraList = new List<string>(){"@p_accno1", "@p_accno2"};
            }
            if (!string.Empty.Equals(p_accno1) & string.Empty.Equals(p_accno2))
            {
                v1 = " AND A.acc_no = @acc_no1";
                valueLst = new List<string>() { p_accno1};
                paraList = new List<string>() { "@p_accno1"};
            }
            if (string.Empty.Equals(p_accno1) & !string.Empty.Equals(p_accno2))
            {
                v1 = " AND A.acc_no = @acc_no2";
                valueLst = new List<string>() { p_accno2 };
                paraList = new List<string>() { "@p_accno2" };
            }
            if (!string.Empty.Equals(v1))
            {
                FillFieldList(valueLst.ToArray(), paraList.ToArray(), "acc_no");
            }
            
            return v1;
        }

        protected string getSQLStatement()
        {
            return @"
SELECT 
    A.acc_no
    ,(SELECT ACC_NAME FROM artm.dbo.AC001 WHERE ACC_NO = A.acc_no) ACC_NAME
    ,A.big
    ,A.small
    ,A.branch
    ,A.cur
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 1 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [1月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 2 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [2月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 3 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [3月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 4 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [4月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 5 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [5月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 6 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [6月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 7 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [7月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 8 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [8月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 9 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [9月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 10 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [10月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 11 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [11月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 12 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [12月]
FROM artm.dbo.ARTM95_ABCDE A 
WHERE 1=1
{0}
{1}
{2}

GROUP BY A.acc_no,A.big,A.small,A.cur,A.branch
";
        }

        protected string getSQLStatementWith123()
        {
            //string sBetweenDate = getBetweenDateString(p_year);
            /*
             --AND A.acc_no IN ('1179001000','1253000000') →1
             --AND A.big = 'A'  →2
             --AND A.small = '1' →3
             --AND A.cur = 'TWD'
             --AND A.branch = '021'
             */

            return @"
SELECT
	A.acc_no
    ,(SELECT ACC_NAME FROM artm.dbo.AC001 WHERE ACC_NO = A.acc_no) ACC_NAME
    ,A.big,A.small,A.cur,A.branch	
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 1 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [1月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 2 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [2月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 3 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [3月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 4 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [4月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 5 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [5月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 6 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [6月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 7 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [7月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 8 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [8月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 9 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [9月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 10 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [10月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 11 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [11月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 12 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [12月]
	
    ,CONVERT(VARCHAR, CAST(SUM(A.bal_2 * DAY(A.ymd)) /	
	(SELECT SUM(DD) AS DAYS
		FROM (
		SELECT day(A.ymd) DD
		FROM artm.dbo.ARTM95_ABCDE A 
		WHERE 1=1
        {0}
        {2}
		GROUP BY A.ymd
		)A) AS MONEY ),1)
	

	 AS [累計均額]
	
FROM artm.dbo.ARTM95_ABCDE A 
WHERE 1=1 
{0}
{1}
{2}

GROUP BY A.acc_no,A.big,A.small,A.cur,A.branch";
        }

        protected string getBetweenDateString(string p_year)
        {
            string sDate1, sDate2, sBetweenDate;
            sDate1 = Utility.TheYearFirstDay(p_year);
            sDate2 = Utility.TheYearLastDay(p_year);
            sBetweenDate = string.Format("AND A.ymd BETWEEN CONVERT(DATETIME,'{0}',111) AND CONVERT(DATETIME,'{1}',111)"
                , sDate1, sDate2);
            return sBetweenDate;        
        }

        protected string getSQLStatementWith45()
        {
            //string sBetweenDate = getBetweenDateString(p_year);
            return @"
SELECT
    A.acc_no
    ,MAX(B.ACC_NAME) ACC
    ,A.big
    ,A.small
    ,A.branch
    ,A.cur
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 1 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [1月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 2 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [2月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 3 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [3月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 4 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [4月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 5 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [5月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 6 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [6月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 7 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [7月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 8 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [8月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 9 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [9月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 10 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [10月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 11 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [11月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 12 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [12月]
    ,CONVERT(VARCHAR, CAST(MAX(c.total) AS MONEY ),1) AS [累計數]
FROM artm.dbo.ARTM95_ABCDE A 
    LEFT OUTER JOIN artm.dbo.AC001 B ON A.acc_no = B.ACC_NO 
	LEFT OUTER JOIN 
	                (
		                SELECT A.acc_no
			                ,A.big
			                ,A.small
			                ,A.branch
			                ,A.cur
			                ,SUM(A.bal_2) [TOTAL]
		                FROM artm.dbo.ARTM95_ABCDE A 
		                WHERE 1=1 
                            {0}
			                {1}
                            {2}
		                GROUP BY  
		                   A.acc_no
			                ,A.big
			                ,A.small
			                ,A.branch
			                ,A.cur	
	                ) C ON A.acc_no= C.acc_no AND A.big = C.big 
                            AND A.small = C.small AND A.branch = C.branch 
                            AND A.cur = C.cur
WHERE 1=1 
    {0}
    {1}
    {2}
GROUP BY  
   A.acc_no
    ,A.big
    ,A.small
    ,A.branch
    ,A.cur";
        }

        public override string[] GetDistinctField()
        {
            return new string[]{
                             "big"
                            ,"small"
                            ,"branch"
                            };
        }

        public string[] GetFeildForExcle()
        {
            return new string[]{"","big","small","branch","cur" };
        }

        protected string getSQLWMGStatement()
        {
            return @"
SELECT 
    A.acc_no
    ,(SELECT ACC_NAME FROM artm.dbo.AC001 WHERE ACC_NO = A.acc_no) ACC_NAME
    {0}
    ,MAX(CASE WHEN YMD = 1 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [1月]
    ,MAX(CASE WHEN YMD = 2 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [2月]
    ,MAX(CASE WHEN YMD = 3 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [3月]
    ,MAX(CASE WHEN YMD = 4 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [4月]
    ,MAX(CASE WHEN YMD = 5 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [5月]
    ,MAX(CASE WHEN YMD = 6 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [6月]
    ,MAX(CASE WHEN YMD = 7 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [7月]
    ,MAX(CASE WHEN YMD = 8 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [8月]
    ,MAX(CASE WHEN YMD = 9 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [9月]
    ,MAX(CASE WHEN YMD = 10 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [10月]
    ,MAX(CASE WHEN YMD = 11 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [11月]
    ,MAX(CASE WHEN YMD = 12 THEN CONVERT(VARCHAR, CAST(A.bal AS MONEY ),1) ELSE '' END) [12月]
FROM (
        SELECT 
        A.ACC_NO
        ,SUM(A.BAL) AS BAL
        ,MONTH(CONVERT(VARCHAR,A.ymd,120)) YMD
        {0}
        FROM artm.dbo.ARTM95_ABCDE A
        WHERE 1=1
        {1}
        {2}
        GROUP BY A.acc_no,MONTH(CONVERT(VARCHAR,A.ymd,120)) {0}
    ) A 
GROUP BY A.acc_no {0}
ORDER BY A.ACC_NO {0}
";
        }

        public void SetGroupByString(string p_group)
        {
            groupString = p_group;
        }

        /// <summary>
        /// 總數：001、002、OBU、海外、台幣、外幣
        /// </summary>
        /// <returns></returns>
        protected string get001Statement(object[] p_para)
        {
            /*
             * {0} 欄位
             * {1}{2} 原本的條件式
             * {3} 這一組的條件，EX:AND A.CUR = '555' OR AND A.CUR = 'TWD'
             * {4} 計算用的欄位，EX:bal_2 or bal
             */
            return string.Format(@"
    	SELECT 
		 {0} AS category
		 ,CASE WHEN MONTH(A.YMD) =  1 THEN SUM(A.{4}) ELSE 0 END [1月]
		 ,CASE WHEN MONTH(A.YMD) =  2 THEN SUM(A.{4}) ELSE 0 END [2月]
		 ,CASE WHEN MONTH(A.YMD) =  3 THEN SUM(A.{4}) ELSE 0 END [3月]
		 ,CASE WHEN MONTH(A.YMD) =  4 THEN SUM(A.{4}) ELSE 0 END [4月]
		 ,CASE WHEN MONTH(A.YMD) =  5 THEN SUM(A.{4}) ELSE 0 END [5月]
		 ,CASE WHEN MONTH(A.YMD) =  6 THEN SUM(A.{4}) ELSE 0 END [6月]
		 ,CASE WHEN MONTH(A.YMD) =  7 THEN SUM(A.{4}) ELSE 0 END [7月]
		 ,CASE WHEN MONTH(A.YMD) =  8 THEN SUM(A.{4}) ELSE 0 END [8月]
		 ,CASE WHEN MONTH(A.YMD) =  9 THEN SUM(A.{4}) ELSE 0 END [9月]
		 ,CASE WHEN MONTH(A.YMD) = 10 THEN SUM(A.{4}) ELSE 0 END [10月]
		 ,CASE WHEN MONTH(A.YMD) = 11 THEN SUM(A.{4}) ELSE 0 END [11月]
		 ,CASE WHEN MONTH(A.YMD) = 12 THEN SUM(A.{4}) ELSE 0 END [12月]
		 ,SUM(A.{4})  T1
		 ,DAY(A.ymd)  T2
		 ,SUM(A.{4}) * DAY(A.ymd) T3	 	 
	FROM artm.dbo.ARTM95_ABCDE A 
	WHERE 1=1 
	 {1}
     {2}
	 {3}
	GROUP BY A.ymd", p_para); 
        }

        protected string getTotalStatement()
        {
            return @"
SELECT
	'' AS ACC_NO
    ,A.category AS ACC_NAME
    ,'' AS Big
    ,'' AS Small
    ,'' AS Branch
    ,'' AS CUR	
	,CONVERT(VARCHAR,CAST(SUM(A.[1月])AS MONEY),120) AS [1月]
	,CONVERT(VARCHAR,CAST(SUM(A.[2月])AS MONEY),120) AS [2月]
	,CONVERT(VARCHAR,CAST(SUM(A.[3月])AS MONEY),120) AS [3月]
	,CONVERT(VARCHAR,CAST(SUM(A.[4月])AS MONEY),120) AS [4月]
	,CONVERT(VARCHAR,CAST(SUM(A.[5月])AS MONEY),120) AS [5月]
	,CONVERT(VARCHAR,CAST(SUM(A.[6月])AS MONEY),120) AS [6月]
	,CONVERT(VARCHAR,CAST(SUM(A.[7月])AS MONEY),120) AS [7月]
	,CONVERT(VARCHAR,CAST(SUM(A.[8月])AS MONEY),120) AS [8月]
	,CONVERT(VARCHAR,CAST(SUM(A.[9月])AS MONEY),120) AS [9月]
	,CONVERT(VARCHAR,CAST(SUM(A.[10月])AS MONEY),120) AS [10月]
	,CONVERT(VARCHAR,CAST(SUM(A.[11月])AS MONEY),120) AS [11月]
	,CONVERT(VARCHAR,CAST(SUM(A.[12月])AS MONEY),120) AS [12月]
	{0}
FROM(
	{1}
)A GROUP BY category

UNION ALL

{2}
";
        }

        protected string getDebtStatement()
        {
            return @"
SELECT branch,cur,big,small,acc_no,bal,bal_2,ymd
FROM artm.dbo.ARTM95_ABCDE
WHERE big = 'C'
AND small =  '9'
AND ymd = @ymd
AND (acc_no LIKE '1168%'
    OR acc_no LIKE '1431%'
    OR acc_no LIKE '1445%'
    OR acc_no LIKE '1751%'
    OR acc_no LIKE '3412%'
    OR acc_no LIKE '4501%'
    OR acc_no LIKE '4805%'
    OR acc_no LIKE '5805%'
    OR acc_no LIKE '5271%'
    OR acc_no LIKE '4271%'
    OR acc_no LIKE '5516%'
    OR acc_no LIKE '5601%')
ORDER BY acc_no
";
            /*
            return @"
SELECT branch,cur,big,small,acc_no,bal,bal_2,ymd
FROM ARTM95_ABCDE
WHERE big = 'C'
AND small =  '9'
AND ymd = @ymd
AND acc_no IN ('1168002001','1168008000'
,'1168019000','1431007002','1431019002','1431019003','1445007002','1445007006'
,'1445007003','1445006005','1445007013','1431005006','1431005001','1445008003'
,'1445008001','1751007002','4501048002','4501048004','4501035005','4501035016'
,'4501035017','4501049002','4501049009','4501049010','4501046003','5501046003'
,'3412002000','4501042002','4501042007','4501042003','4805002000','5805002000'
,'4805007000','5805007000','4501042008','4501050002','4501050006','4501050007'
,'4501042004','4501048008','4271007000','5271007000','4501035004','3412005000'
,'4501042009','4271018000','5271018000','4501043002','4516099000','5516099000'
,'5601000000','5602000000','4262007000','5261017000')
ORDER BY acc_no
";
             */ 
        }

        protected string getDismantleStatement()
        { 
            return @"
SELECT branch,cur,big,small,acc_no
     ,CONVERT(VARCHAR,CAST(bal AS MONEY),1) 餘額
     ,CONVERT(VARCHAR,CAST(bal_2 AS MONEY),1) 積數
    ,ymd
FROM artm.dbo.ARTM95_ABCDE
WHERE ymd = @ymd
    AND acc_no IN ('2123011001','2123012001','2123099000'
                    ,'5501053008','5501053004','5501053001')
        {0}
ORDER BY acc_no
";
        
        }

        protected string getYieldStatement()
        {
            return @"
SELECT * FROM (
--資金科

  SELECT branch,cur,big,small,acc_no
    ,CONVERT(nVARCHAR,CAST(round(bal,0) AS MONEY),1) 餘額
    ,CONVERT(VARCHAR,CAST(bal_2 AS MONEY),1) 均額
    ,ymd
  FROM artm.dbo.ARTM95_ABCDE
WHERE ymd = @ymd --'2012-06-30'  
AND big IN ('8')
AND small IN ('7')
AND acc_no IN ('1431001001','1431001002','2162001000')

UNION ALL
--IRDFH
 SELECT branch,cur,big,small,acc_no
    ,CONVERT(nVARCHAR,CAST(round(bal,0) AS MONEY),1) 餘額
    ,CONVERT(VARCHAR,CAST(bal_2 AS MONEY),1) 均額
    ,ymd
  FROM artm.dbo.ARTM95_ABCDE
WHERE ymd = @ymd --'2012-06-30'  
AND big IN ('F')
AND small IN ('H')
AND cur IN ('TWD')
AND acc_no IN 
('1164010001','1164010002','1164011001','2162007001'
,'2162007002','2162008000','4501020010','4252010000'
,'4501020018','4252024008','5251024008','5251010000'
,'4501020011','4501020019','4252024009','5251024009'
,'4252011000','5251011000','5501027004','5501027018'
,'5501027005','5501027019')

UNION ALL

--台幣債
SELECT branch,cur,big,small,acc_no
    ,CONVERT(nVARCHAR,CAST(round(bal,0) AS MONEY),1) 餘額
    ,CONVERT(VARCHAR,CAST(bal_2 AS MONEY),1) 均額
    ,ymd
  FROM artm.dbo.ARTM95_ABCDE
WHERE ymd = @ymd --'2012-06-30'  
AND big IN ('F')
AND small IN ('6')
AND cur IN ('TWD')
AND acc_no IN 
('1431001001','1431001002','1164001000','1431003001'
,'1431003002','1164003000','1445003002','1431002001'
,'1431002002','1431002003','1164002000','1751002003'
,'1445002001','1751018000','1751006001','1751006003'
,'2162001000','2162001000','2162002000','2162002000'
,'2162003000','2162003000','4501035001','4501049001'
,'4501020001','4501035003','4501049003','4501020003'
,'4501042003','4501050003','4501035002','4501049002'
,'4501043002','4501020002','4501051002','4501042002'
,'4501050002','4501041001','4501041002','4501051004'
,'4501051004','4501043005','4501051005','5501025001'
,'5501025001','5501025002','5501025002','5501025003'
,'5501025003','4501032001','4501032001','5501032001')
)A
ORDER BY A.acc_no
";
        }

        protected string getTicketStatement()
        {
            return @"
SELECT 
	YEAR([ymd]) YYYY
	,[branch]
      ,[cur]
      ,[big]
      ,[small]
      ,[acc_no]
      ,[bal_2]
  FROM artm.dbo.[ARTM95_ABCDE]
WHERE ymd = @ymd
AND
(
acc_no LIKE '1164%'
OR acc_no LIKE '1168%'
OR acc_no LIKE '1431%'
OR acc_no LIKE '1445%'
OR acc_no LIKE '1751%'
OR acc_no LIKE '4501%'
OR acc_no LIKE '4805%'
OR acc_no LIKE '5805%'
OR acc_no = '4252011000'
OR acc_no = '4252010000'
OR acc_no = '5251011000' 
OR acc_no = '5251010000'
OR acc_no LIKE '5501046%'        
)
ORDER BY acc_no,branch
";

        
        }

        /// <summary>
        /// 為了讓它能在excel變成字串，多加了符號在指令中
        /// </summary>
        /// <returns></returns>
        protected string getSaveCenStatement()
        {
            return @"
SELECT 
	YEAR([ymd]) YYYY
	,'=""' + [branch] + '""' as branch
      ,[cur]
      ,'=""' + [big] + '""' as big
      ,'=""' + [small] + '""' as small
      ,'=""' + [acc_no] + '""' as acc_no
      ,[bal_2]
  FROM artm.dbo.[ARTM95_ABCDE]
WHERE ymd = @ymd
AND (
acc_no LIKE '11%'
OR acc_no LIKE '13%'
OR acc_no LIKE '1812%'		
OR acc_no LIKE '4501%'
OR Acc_no LIKE '5501%'
OR acc_no LIKE '5929008%'
OR acc_no LIKE '21%'
OR acc_no LIKE '2321028%'
OR acc_no LIKE '2371%'
OR acc_no LIKE '24%'
OR acc_no LIKE '2511%'
)
ORDER BY acc_no,branch
";
        }

        /// <summary>
        /// 收入費用
        /// </summary>
        /// <returns></returns>
        protected string getIncomeCostStatement()
        {
            return @"

SELECT 
	'=""' + A.acc_no + '""' as acc_no
	,B.ACC_NAME AS ACC 
	,'=""' + [big] + '""' as big
	,'=""' + [small] + '""' as small
	,A.NA
	,CONVERT(VARCHAR, CAST([1月] AS MONEY ),1)[1月]
	,CONVERT(VARCHAR, CAST([2月] AS MONEY ),1)[2月]
	,CONVERT(VARCHAR, CAST([3月] AS MONEY ),1)[3月]
	,CONVERT(VARCHAR, CAST([4月] AS MONEY ),1)[4月]
	,CONVERT(VARCHAR, CAST([5月] AS MONEY ),1)[5月]
	,CONVERT(VARCHAR, CAST([6月] AS MONEY ),1)[6月]
	,CONVERT(VARCHAR, CAST([7月] AS MONEY ),1)[7月]
	,CONVERT(VARCHAR, CAST([8月] AS MONEY ),1)[8月]
	,CONVERT(VARCHAR, CAST([9月] AS MONEY ),1)[9月]
	,CONVERT(VARCHAR, CAST([10月] AS MONEY ),1)[10月]
	,CONVERT(VARCHAR, CAST([11月] AS MONEY ),1)[11月]	
	,CONVERT(VARCHAR, CAST([12月] AS MONEY ),1)[12月]	
FROM(
		SELECT
			A.acc_no
			,A.big
			,A.small
		
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 1 THEN A.bal_2 ELSE 0 END) [1月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 2  THEN A.bal_2 ELSE 0 END) [2月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 3  THEN A.bal_2 ELSE 0 END) [3月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 4 THEN A.bal_2 ELSE 0 END) [4月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 5 THEN A.bal_2 ELSE 0 END) [5月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 6 THEN A.bal_2 ELSE 0 END) [6月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 7 THEN A.bal_2 ELSE 0 END) [7月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 8 THEN A.bal_2 ELSE 0 END) [8月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 9 THEN A.bal_2 ELSE 0 END) [9月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 10 THEN A.bal_2 ELSE 0 END) [10月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 11 THEN A.bal_2 ELSE 0 END) [11月]
			,SUM(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 12 THEN A.bal_2 ELSE 0 END) [12月]
			,CASE 
				WHEN A.branch = '950' THEN 'HK'
				WHEN A.branch IN('960','962','963') THEN 'VN'
				ELSE 'TP'  END NA  
		FROM artm.dbo.ARTM95_ABCDE A 
		WHERE (YMD BETWEEN CAST('{0}-01-01' AS DATE)  AND CAST('{0}-12-31' AS DATE))
			AND (a.acc_no LIKE '4%' OR a.acc_no LIKE '5%')
			AND big IN {1}
		GROUP BY  
			A.acc_no
			,A.big
			,A.small
			,CASE 
				WHEN A.branch = '950' THEN 'HK'
				WHEN A.branch IN('960','962','963') THEN 'VN'
				ELSE 'TP'  END
			
)A LEFT OUTER JOIN artm.dbo.AC001 B
ON A.ACC_NO = B.ACC_NO				
";
            /*
            return @"
SELECT
	'=""' + A.acc_no + '""' as acc_no
	,acc
	,'=""' + [big] + '""' as big
	,'=""' + [small] + '""' as small
	,A.NA
	,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 1 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [1月]
	,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 2 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [2月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 3 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [3月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 4 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [4月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 5 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [5月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 6 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [6月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 7 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [7月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 8 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [8月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 9 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [9月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 10 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [10月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 11 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [11月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 12 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [12月]
FROM 
(
	SELECT
		A.acc_no
		,acc
		,A.ymd
		,A.big
		,A.small
		,SUM(A.bal_2)bal_2  
		,CASE 
				WHEN A.branch = '950' THEN 'HK'
				WHEN A.branch IN('960','962','963') THEN 'VN'
				ELSE 'TP'  END NA  
	FROM
	(
		SELECT
			A.acc_no
			,MAX(B.ACC_NAME) ACC
			,A.big
			,A.small
			,A.cur
			,MAX(A.bal_2) BAL_2
			,A.ymd	
			,branch
		FROM ARTM95_ABCDE A with(index(IX_ARTM95_ABCDE)) 
			LEFT OUTER JOIN AC001 B ON A.acc_no = B.ACC_NO 	
		WHERE (YMD BETWEEN CAST('{0}-01-01' AS DATE)  AND CAST('{0}-12-31' AS DATE))
			AND (a.acc_no LIKE '4%' 
					OR a.acc_no LIKE '5%'	
				)
		  AND big IN {1}
		GROUP BY  
			A.acc_no
			,A.big
			,A.small
			,branch
			,A.cur
			,A.ymd
	)A	
	GROUP BY
		  acc_no
		  ,acc
		  ,big
		  ,small
		  ,ymd
		  ,CASE 
				WHEN A.branch = '950' THEN 'HK'
				WHEN A.branch IN('960','962','963') THEN 'VN'
				ELSE 'TP'  END 
)A
GROUP BY
		  acc_no
		  ,acc
		  ,big
		  ,small
		  ,NA			
";*/
        }

        protected string getIncomeCostBetween2YStatement()
        {
            /*
             * {0} YEAR1
             * {1} YEAR2 兩年比較的年份
             * {2} MONTH
             */
            return @"
SELECT
	'=""' + A.acc_no + '""' as acc_no
	,acc
	,A.big
	,'=""' + [small] + '""' as small
	,A.NA
    ,MAX(CASE WHEN YEAR(ymd) = {0} THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [{0}_{2}月]
    ,MAX(CASE WHEN YEAR(ymd) = {1} THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [{1}_{2}月]
FROM 
(
	SELECT
		A.acc_no
		,acc
		,A.ymd
		,A.big
		,A.small
		,SUM(A.bal_2)bal_2  
		,CASE 
				WHEN A.branch = '950' THEN 'HK'
				WHEN A.branch IN('960','962','963') THEN 'VN'
				ELSE 'TP'  END NA 
	FROM
	(
		SELECT
			A.acc_no
			,MAX(B.ACC_NAME) ACC
			,A.big
			,A.small
			,A.cur
			,MAX(A.bal_2) BAL_2
			,A.ymd	
			,branch
		FROM artm.dbo.ARTM95_ABCDE A 
			LEFT OUTER JOIN artm.dbo.AC001 B ON A.acc_no = B.ACC_NO 	
		WHERE 1=1 
			AND year(A.ymd)in({0},{1})
			AND (a.acc_no LIKE '4%' 
					OR a.acc_no LIKE '5%'
				)		
			and MONTH(a.ymd) = {2}
			AND big = 'F'
			AND small in ('G','F','6','H','B','C','Q','R')
		GROUP BY  
			A.acc_no
			,A.big
			,A.small
			,branch
			,A.cur
			,A.ymd
	)A	
	GROUP BY
		  acc_no
		  ,acc
		  ,big
		  ,small
		  ,ymd
		  ,CASE 
				WHEN A.branch = '950' THEN 'HK'
				WHEN A.branch IN('960','962','963') THEN 'VN'
				ELSE 'TP'  END 
)A
GROUP BY
		  acc_no
		  ,acc
		  ,big
		  ,small
		  ,NA			
ORDER BY 		   
		  big
		  ,small		  
		  ,NA	
		  ,acc_no	
";
        }

        protected string getBusinessCostStatement()
        {
            /*
             * {0}year(ymd)
             * {1}A.acc_no LIKE '5811%' OR A.acc_no = '5832000000'
             * {2}('8','F')
             * {3}('6','2','7','8','B','C','P','Q','R','G','F','H','U')
             */
            return @"
SELECT 
	'=""' + A.acc_no + '""' as acc_no
    ,MAX(B.ACC_NAME) ACC
	,'=""' + A.branch + '""' as branch
	,a.cur
	,'=""' + [big] + '""' as big
	,'=""' + [small] + '""' as small
	,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 1 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [1月]
	,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 2 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [2月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 3 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [3月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 4 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [4月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 5 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [5月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 6 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [6月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 7 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [7月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 8 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [8月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 9 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [9月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 10 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [10月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 11 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [11月]
    ,MAX(CASE WHEN MONTH(CONVERT(VARCHAR,A.ymd,120)) = 12 THEN CONVERT(VARCHAR, CAST(A.bal_2 AS MONEY ),1) ELSE '' END) [12月]
FROM artm.dbo.ARTM95_ABCDE A LEFT OUTER JOIN artm.dbo.AC001 B
    ON A.acc_no = B.ACC_NO
WHERE YEAR(A.ymd) = {0}
AND ({1} )
--AND A.big = '8' AND A.small ='8'
--AND A.branch = '190'
AND A.big IN {2}
AND A.small IN {3}
GROUP BY A.big,A.small,A.acc_no,A.branch,a.cur
ORDER BY A.big,A.small,A.acc_no,A.branch
            
            ";
        }

        protected string getFMGYieldStatement()
        {
            return @"
SELECT
	'=""' + A.acc_no + '""' as acc_no
    ,B.ACC_NAME ACC
	,'=""' + A.branch + '""' as branch
	,'=""' + [big] + '""' as big
	,'=""' + [small] + '""' as small
	,CASE WHEN ( A.acc_no LIKE '4%' OR A.acc_no LIKE '5%')THEN '0' ELSE 餘額 END 餘額
	,CASE WHEN ( A.acc_no LIKE '4%' OR A.acc_no LIKE '5%')THEN '0' ELSE 均額 END 均額
	,CASE WHEN ( A.acc_no LIKE '4%' OR A.acc_no LIKE '5%') THEN 均額 ELSE '0' END 當月發生數
FROM
(
SELECT branch,cur,big,small,acc_no
    ,CONVERT(nVARCHAR,CAST(round(bal,0) AS MONEY),1) 餘額
    ,CONVERT(VARCHAR,CAST(bal_2 AS MONEY),1) 均額
    ,ymd
  FROM artm.dbo.ARTM95_ABCDE
WHERE ymd = @ymd --'2012-09-30'
AND big IN ('8')
AND small IN ('7')
AND cur IN ('TWD')
AND acc_no IN ('1431001001','1431001002','1445001001','1445001002','1431002001'
,'1445002001','1431010002','1445010002','1445012001','1445012002'
,'1431013000','2162001000','2162002000','2162003000','2162007000'
,'2162008000','4501035001','4501042001','4501049001','4501050001'
,'4501035002','4501042002','4501049002','4501035018','4501042018'
,'4501042020','4501035021','5501025001','5501025002','5501025003'
,'5501027018','5501027019'
,'1162001000','1162003000','1162007000','1431002002'
,'1431011000','1445003002','1751002003','1751006003'
,'1751018000','2162007001','4501032003','4501033018'
,'4501041001','4501041002','4501042003','4501043002'
,'4501049010','4501049011','4501050002','4501050003'
,'4501051002','4501051005','4805018000','5805018000'
,'5929008001','5929008003','1162002000'
,'1162004000','1162005000','1162006000'
,'1162009000','2162004000','2162006000'
,'2162009000','2162010000','4501032002'
,'4501033001','4501033002','4501033003'
,'4501033004','4501033005','4501033019'
,'4501033020','4501033021','4501033022'
,'4501033023','5501027001','5501027002'
,'5501027003','5501027004','5501027005'
,'5501027020','5501027021','5501027022'
,'5501027023','5501027024','2123011001'
,'2123012001','5501053001','5501053004'
,'2371001000'
,'2371002000','2371003000','2371004000'
,'5501045001','5501045002','5501045003'
,'5501045004','5501045005','5501045006'
)


UNION ALL

SELECT branch,cur,big,small,acc_no
    ,CONVERT(nVARCHAR,CAST(round(bal,0) AS MONEY),1) 餘額
    ,CONVERT(VARCHAR,CAST(bal_2 AS MONEY),1) 均額
    ,ymd
  FROM artm.dbo.ARTM95_ABCDE
WHERE ymd = @ymd 
AND big IN ('F')
AND small IN ('H')
AND cur IN ('TWD')
AND acc_no IN 
('1164010001','1164010002','1164011001','2162007001'
,'2162007002','2162008000','4501020018','4252024008'
,'4501020019','4252024009','5251024009','5501027018'
,'5501027019','1162001000','1162003000','1162007000','1431002002'
,'1431011000','1445003002','1751002003','1751006003'
,'1751018000','2162007001','4501032003','4501033018'
,'4501041001','4501041002','4501042003','4501043002'
,'4501049010','4501049011','4501050002','4501050003'
,'4501051002','4501051005','4805018000','5805018000'
,'5929008001','5929008003','1162002000'
,'1162004000','1162005000','1162006000'
,'1162009000','2162004000','2162006000'
,'2162009000','2162010000','4501032002'
,'4501033001','4501033002','4501033003'
,'4501033004','4501033005','4501033019'
,'4501033020','4501033021','4501033022'
,'4501033023','5501027001','5501027002'
,'5501027003','5501027004','5501027005'
,'5501027020','5501027021','5501027022'
,'5501027023','5501027024','2123011001'
,'2123012001','5501053001','5501053004'
,'2371001000'
,'2371002000','2371003000','2371004000'
,'5501045001','5501045002','5501045003'
,'5501045004','5501045005','5501045006'
)

UNION ALL


SELECT branch,cur,big,small,acc_no
    ,CONVERT(nVARCHAR,CAST(round(bal,0) AS MONEY),1) 餘額
    ,CONVERT(VARCHAR,CAST(bal_2 AS MONEY),1) 均額
    ,ymd
  FROM artm.dbo.ARTM95_ABCDE
WHERE ymd = @ymd 
AND big IN ('F')
AND small IN ('6')
AND cur IN ('TWD')
AND acc_no IN 
('1431001001','1431001002','1164001000','1431003001','1431003002'
,'1164003000','1445003002','1431002001','1431002002','1431002003'
,'1164002000','1751002003','1445002001','1751018000','1751006001'
,'1751006003','2162001000','2162002000','2162003000'
,'4501035001','4501049001','4501020001','4501035003','4501049003'
,'4501020003','4501042003','4501050003','4501035002','4501049002'
,'4501043002','4501020002','4501051002','4501042002','4501050002'
,'4501041001','4501041002','4805018000','5805018000','4501051004'
,'4501051004','4501043005','4501051005','4805006000','5805006000'
,'5501025001','5501025002','5501025003','5929008001','5929008003'
,'4252017015','4253017015','4254017015','4255017015','5251017015'
,'5252017015','5253017015','5254017015','4252017001','4253017001'
,'5253017001','5254017001','4262001000','5261001000','5535003000'
,'4252001000','4252001000','5251001000','5251001000','4253001000'
,'4253001000','5252001000','5252001000','4254019001','5253019001'
,'5254019001','4501032001','4501032001','5501032001','4253003000'
,'5252003000','1162001000','1162003000','1162007000','1431002002'
,'1431011000','1445003002','1751002003','1751006003'
,'1751018000','2162007001','4501032003','4501033018'
,'4501041001','4501041002','4501042003','4501043002'
,'4501049010','4501049011','4501050002','4501050003'
,'4501051002','4501051005','4805018000','5805018000'
,'5929008001','5929008003','1162002000'
,'1162004000','1162005000','1162006000'
,'1162009000','2162004000','2162006000'
,'2162009000','2162010000','4501032002'
,'4501033001','4501033002','4501033003'
,'4501033004','4501033005','4501033019'
,'4501033020','4501033021','4501033022'
,'4501033023','5501027001','5501027002'
,'5501027003','5501027004','5501027005'
,'5501027020','5501027021','5501027022'
,'5501027023','5501027024','2123011001'
,'2123012001','5501053001','5501053004'
,'2371001000'
,'2371002000','2371003000','2371004000'
,'5501045001','5501045002','5501045003'
,'5501045004','5501045005','5501045006'
    )
)A LEFT OUTER JOIN  artm.dbo.AC001 B
ON A.acc_no = B.ACC_NO

";
        }

        protected string getOperatingExpStatement()
        {
            /*
             * {0} 大版塊A.big IN ('1','C','D','E','L','N')
             * {1}{2} 日期區間
             */
            return @"
SELECT * FROM (
SELECT	
    CASE WHEN B.ymd IS NULL THEN '1900-01-01'ELSE CONVERT(CHAR(12), B.ymd,111) END YMD
	,'STEP1' ACC
	,A.九類
	,'STEP1' [科目名稱]
	,'=""' + B.big + '""' as  BIG
	,sum(B.bal_2)AMOUNT
FROM artm.dbo.GL_ACC_MAP_TABLE A LEFT OUTER JOIN
(
	SELECT
		A.ymd
		,A.acc_no
		,A.big
		,bal_2
	FROM artm.dbo.ARTM95_ABCDE A
	WHERE A.ymd BETWEEN @ymd1 AND @ymd2
	AND (A.ACC_NO LIKE '4%'or A.ACC_NO LIKE '5%')
	 {0}
)B ON A.[會科] = B.acc_no
GROUP BY B.YMD,A.[九類],B.big

UNION ALL

SELECT
 CONVERT(CHAR(12), B.ymd,111) YMD
 ,'=""' + A.ACC + '""' acc
 ,A.九類
 ,A.[科目名稱]
 ,'=""' + B.big + '""' as big
 ,isnull(SUM(B.BAL_2),-1) AMOUNT
FROM (
SELECT
	A.[新子目] + A.[新細目] ACC
	,A.九類
	,A.[科目名稱]
    ,A.[會科]
FROM artm.dbo.GL_ACC_MAP_TABLE A
GROUP BY A.[新子目] + A.[新細目] 
    ,A.[會科]	
    ,A.九類
	,A.[科目名稱]
 
) A LEFT OUTER JOIN
(
	SELECT		
		ymd
		,SUBSTRING(A.acc_no,5,10)ACC
		,A.acc_no
		,A.big
		,bal_2
	FROM artm.dbo.ARTM95_ABCDE A
	WHERE A.ymd BETWEEN @ymd1 AND @ymd2
	AND (A.ACC_NO LIKE '4%'or A.ACC_NO LIKE '5%')
	 {0}
)B ON A.ACC = B.ACC AND A.[會科] = B.ACC_NO
GROUP BY 
	B.ymd
	,A.ACC
	,A.九類
	,A.[科目名稱]
	,B.BIG
)A WHERE A.YMD > CAST('1900-01-01' AS DATE)
";
        }

        protected string getOperatingExpDetailStatement()
        {
            /*{0} 原本的條件
             * 大版有多少，就要產生出多少
             * {1} 欄位,SUM(CASE WHEN big = '1' THEN B.bal_2 ELSE 0 END)AMOUNT_1
             */
            return @"
SELECT * FROM (
SELECT
 CASE WHEN B.ymd IS NULL THEN '1900-01-01'ELSE CONVERT(CHAR(12), B.ymd,111) END YMD
 ,'=""' + A.ACC + '""' ACC
 ,A.九類
 ,A.[科目名稱]
 {1}   
FROM (
SELECT
	A.[新子目] + A.[新細目] ACC
	,A.九類
	,A.[科目名稱]
    , A.會科
FROM artm.dbo.GL_ACC_MAP_TABLE A
GROUP BY A.[新子目] + A.[新細目] 
	,A.九類
    ,A.會科
	,A.[科目名稱]
 
) A LEFT OUTER JOIN
(
	SELECT		
		ymd
		,SUBSTRING(A.acc_no,5,10)ACC
		,A.acc_no
		,A.big
		,bal_2
	FROM artm.dbo.ARTM95_ABCDE A
	WHERE A.ymd BETWEEN @ymd1 AND @ymd2
	AND (A.ACC_NO LIKE '4%'or A.ACC_NO LIKE '5%')
	 {0}
)B ON A.ACC = B.ACC AND A.會科 = B.acc_no
GROUP BY 
	B.ymd
	,A.ACC
	,A.九類
	,A.[科目名稱]
) A WHERE A.YMD > '1900-01-01'
";
        }

        protected string getOPerationExpByMonthStatement()
        {
            return @"
SELECT  
A.九類
 ,'=""' + a.big  + '""' big
 ,'=""' + a.small  + '""' big 
 ,A.[1月] 
 ,A.[2月]
 ,A.[3月]
 ,A.[4月]
 ,A.[5月]
 ,A.[6月]
 ,A.[7月]
 ,A.[8月]
 ,A.[9月]
 ,A.[10月]
 ,A.[11月]
 ,A.[12月]
FROM (
SELECT 
 A.九類 
 ,B.big  
 ,B.small 
 ,SUM(CASE WHEN MONTH(B.ymd)=1 THEN B.bal_2 ELSE 0 END) [1月]
 ,SUM(CASE WHEN MONTH(B.ymd)=2 THEN B.bal_2 ELSE 0 END) [2月]
 ,SUM(CASE WHEN MONTH(B.ymd)=3 THEN B.bal_2 ELSE 0 END) [3月]
 ,SUM(CASE WHEN MONTH(B.ymd)=4 THEN B.bal_2 ELSE 0 END) [4月]
 ,SUM(CASE WHEN MONTH(B.ymd)=5 THEN B.bal_2 ELSE 0 END) [5月]
 ,SUM(CASE WHEN MONTH(B.ymd)=6 THEN B.bal_2 ELSE 0 END) [6月]
 ,SUM(CASE WHEN MONTH(B.ymd)=7 THEN B.bal_2 ELSE 0 END) [7月]
 ,SUM(CASE WHEN MONTH(B.ymd)=8 THEN B.bal_2 ELSE 0 END) [8月]
 ,SUM(CASE WHEN MONTH(B.ymd)=9 THEN B.bal_2 ELSE 0 END) [9月]
 ,SUM(CASE WHEN MONTH(B.ymd)=10 THEN B.bal_2 ELSE 0 END) [10月]
 ,SUM(CASE WHEN MONTH(B.ymd)=11 THEN B.bal_2 ELSE 0 END) [11月]
 ,SUM(CASE WHEN MONTH(B.ymd)=12 THEN B.bal_2 ELSE 0 END) [12月] 
FROM (
SELECT
	A.[新子目] + A.[新細目] ACC
	,A.九類
	,A.[科目名稱]
    ,A.會科
FROM artm.dbo.GL_ACC_MAP_TABLE A
GROUP BY A.[新子目] + A.[新細目] 
    ,A.會科		
    ,A.九類
	,A.[科目名稱] 
) A LEFT OUTER JOIN (
	SELECT		
		ymd
		,SUBSTRING(A.acc_no,5,10)ACC
		,A.acc_no
		,A.big
		,a.small
		,bal_2
	FROM artm.dbo.ARTM95_ABCDE A
	WHERE A.ymd BETWEEN @ymd1 AND @ymd2
	AND (A.ACC_NO LIKE '4%'or A.ACC_NO LIKE '5%')
	{0}
)B ON A.ACC = B.ACC AND A.會科 = B.acc_no
GROUP BY 	 	 
	 A.九類
	 ,B.big
	 ,B.small

UNION ALL

SELECT
 'SUBTOT' AS KK
 , B.big  
 , B.small  
 ,SUM(CASE WHEN MONTH(B.ymd)=1 THEN B.bal_2 ELSE 0 END) [1月]
 ,SUM(CASE WHEN MONTH(B.ymd)=2 THEN B.bal_2 ELSE 0 END) [2月]
 ,SUM(CASE WHEN MONTH(B.ymd)=3 THEN B.bal_2 ELSE 0 END) [3月]
 ,SUM(CASE WHEN MONTH(B.ymd)=4 THEN B.bal_2 ELSE 0 END) [4月]
 ,SUM(CASE WHEN MONTH(B.ymd)=5 THEN B.bal_2 ELSE 0 END) [5月]
 ,SUM(CASE WHEN MONTH(B.ymd)=6 THEN B.bal_2 ELSE 0 END) [6月]
 ,SUM(CASE WHEN MONTH(B.ymd)=7 THEN B.bal_2 ELSE 0 END) [7月]
 ,SUM(CASE WHEN MONTH(B.ymd)=8 THEN B.bal_2 ELSE 0 END) [8月]
 ,SUM(CASE WHEN MONTH(B.ymd)=9 THEN B.bal_2 ELSE 0 END) [9月]
 ,SUM(CASE WHEN MONTH(B.ymd)=10 THEN B.bal_2 ELSE 0 END) [10月]
 ,SUM(CASE WHEN MONTH(B.ymd)=11 THEN B.bal_2 ELSE 0 END) [11月]
 ,SUM(CASE WHEN MONTH(B.ymd)=12 THEN B.bal_2 ELSE 0 END) [12月]  
FROM (
SELECT
	A.[新子目] + A.[新細目] ACC
	,A.九類
	,A.[科目名稱]
    ,A.會科
FROM artm.dbo.GL_ACC_MAP_TABLE A
GROUP BY A.[新子目] + A.[新細目] 
    ,A.會科	
    ,A.九類
	,A.[科目名稱]
) A LEFT OUTER JOIN(
	SELECT		
		ymd
		,SUBSTRING(A.acc_no,5,10)ACC
		,A.acc_no
		,A.big
		,a.small
		,bal_2
	FROM artm.dbo.ARTM95_ABCDE A
	WHERE A.ymd BETWEEN @ymd1 AND @ymd2
	AND (A.ACC_NO LIKE '4%'or A.ACC_NO LIKE '5%')
	{0}
)B ON A.ACC = B.ACC AND A.會科 = B.acc_no
GROUP BY 	 	 
	 B.big
	 ,B.small
)A where A.big between '1' and 'Z'
ORDER BY A.big,A.small,A.九類
";
        }

        protected string getOPerationExpByMonthDetailStatement()
        {
            return @"
SELECT * FROM (
SELECT 
 CASE WHEN B.ymd IS NULL THEN '1900-01-01'ELSE CONVERT(CHAR(12), B.ymd,111) END YMD
 ,A.ACC
 ,A.九類 
 ,A.[科目名稱]
 ,B.big
 ,B.small 
 ,sum(B.bal_2)AMOUNT
FROM (
SELECT
	A.[新子目] + A.[新細目] ACC
	,A.九類
	,A.[科目名稱]
    ,A.會科
FROM artm.dbo.GL_ACC_MAP_TABLE A
GROUP BY A.[新子目] + A.[新細目] 
    ,A.會科	
    ,A.九類
	,A.[科目名稱]
) A LEFT OUTER JOIN (
	SELECT		
		ymd
		,SUBSTRING(A.acc_no,5,10)ACC
		,A.acc_no
		,A.big
		,a.small
		,bal_2
	FROM artm.dbo.ARTM95_ABCDE A
	WHERE A.ymd BETWEEN @ymd1 AND @ymd2
	AND (A.ACC_NO LIKE '4%'or A.ACC_NO LIKE '5%')
	{0}
)B ON A.ACC = B.ACC AND A.會科 = B.acc_no
GROUP BY 	 	 
    A.ACC	 
    ,B.ymd
	 ,A.九類
     ,A.[科目名稱]
	 ,B.big
	 ,B.small
)A WHERE A.YMD > CAST('1900-01-01' AS DATE)
";
        }

        protected string getTMUStatement()
        {
            /*12月的處理？？
             * {0} 本期
             * {1} 前期
             * {2} 前一年的本期
             * {3} 本期的第一個月
             * {4} 前一年的本期的第一個月
             */
            return @"
--201X TMU與上一年度同期(累計)業績比較表
SELECT
    'TMUYTD' CAPTION	
    ,'=""' + small + '""' small
	,SUM(RESULT) RESULT
	,SUM(RESULT) /
		CASE WHEN sum(LASTR) > 0 THEN sum(LASTR) 
		ELSE 2 * sum(LASTR) - SUM(RESULT)
		END  per --增減比率(%)
FROM(
	SELECT 
		small
		,ymd
		,CASE WHEN ymd between CAST('2012-01-31' AS DATE) AND CAST('2012-02-29' AS DATE)
				THEN 
				(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
					SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )*-1
				ELSE
				(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
					SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )
				END	result
		,CASE WHEN ymd between CAST('2012-01-31' AS DATE) AND CAST('2012-02-29' AS DATE)
				THEN 
				(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
					SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )
				ELSE 0 END	LASTR
	FROM artm.dbo.ARTM95_ABCDE 
	WHERE (ymd between CAST('{3}' AS DATE) AND CAST('{0}' AS DATE)
	OR ymd between CAST('{4}' AS DATE) AND CAST('{2}' AS DATE))
	AND acc_no IN ('4252000000','4253000000','4254000000','4255000000'
	,'4501000000','4516000000','4534000000','4701000000','5251000000'
	,'5252000000','5253000000','5254000000'	,'5501000000','5516000000'
	,'5534000000','5701000000','5929000000')
	AND big	= 'F' 
	AND small IN ('B','C','Q','R','3','P')
	GROUP BY YMD,small
)A GROUP BY small

UNION ALL

--201X TMU與上一年度同期(本月)業績比較表
SELECT
	'TMU2Y' CAPTION
    ,'=""' + small + '""' small
	,SUM(RESULT) RESULT
	,SUM(RESULT) /
		CASE WHEN sum(LASTR) > 0 THEN sum(LASTR) 
		ELSE 2 * sum(LASTR) - SUM(RESULT)
		END  per --增減比率(%)
FROM
(
SELECT 
	small
	,ymd
	,CASE WHEN ymd = CAST('{2}' AS DATE) 
			THEN 
			(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
				SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )*-1
			ELSE
			(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
				SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )
			END	result
	,CASE WHEN ymd = CAST('{2}' AS DATE) 
			THEN 
			(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
				SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )
			ELSE 0 END	LASTR
FROM artm.dbo.ARTM95_ABCDE 
WHERE (ymd = CAST('{2}' AS DATE) or ymd = CAST('{0}' AS DATE))
AND acc_no IN ('4252000000','4253000000','4254000000','4255000000'
,'4501000000','4516000000','4534000000','4701000000','5251000000'
,'5252000000','5253000000','5254000000','5501000000','5516000000'
,'5534000000','5701000000','5929000000')
AND big	= 'F' 
AND small IN ('B','C','Q','R','3','P')
GROUP BY ymd,small
)A GROUP BY small

UNION ALL

--201X TMU二個月業績比較表
SELECT
	'TMU2M' AS CAPTION
	,'=""' + small + '""' small
	,SUM(RESULT) RESULT
	,SUM(RESULT) /
		CASE WHEN sum(LASTR) > 0 THEN sum(LASTR) 
		ELSE 2 * sum(LASTR) - SUM(RESULT)
		END  per --增減比率(%)
FROM
(
	SELECT 	
		small
		,YMD
		,CASE WHEN ymd = CAST('{1}' AS DATE) 
			THEN 
			(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
				SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )*-1
			ELSE
			(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
				SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )
			END	result
		,CASE WHEN ymd = CAST('{1}' AS DATE) 
			THEN 
			(SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
				SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) )
			ELSE 0 END	LASTR --前期
	FROM artm.dbo.ARTM95_ABCDE 
	WHERE ymd between CAST('{1}' AS DATE) AND CAST('{0}' AS DATE)
	AND acc_no IN ('4252000000','4253000000','4254000000','4255000000'
	,'4501000000','4516000000','4534000000','4701000000','5251000000'
	,'5252000000','5253000000','5254000000'	,'5501000000','5516000000'
	,'5534000000','5701000000','5929000000')
	AND big	= 'F' 
	AND small IN ('B','C','Q','R','3','P')
	GROUP BY YMD,small
)A GROUP BY small

UNION ALL

--201X TMU業績表
SELECT 
	'TMU' AS CAPTION
	,'=""' + small + '""' small
	,SUM(CASE WHEN  acc_no LIKE '4%' THEN BAL_2 ELSE 0 END) -
	SUM(CASE WHEN  acc_no LIKE '5%' THEN BAL_2 ELSE 0 END) RESULT
    ,0.0 PER
FROM artm.dbo.ARTM95_ABCDE 
WHERE ymd = CAST('{0}' AS DATE)
AND acc_no IN ('4252000000','4253000000','4254000000','4255000000'
,'4501000000','4516000000','4534000000','4701000000','5251000000'
,'5252000000','5253000000','5254000000','5501000000','5516000000'
,'5534000000','5701000000','5929000000')
AND big	= 'F' 
AND small IN ('B','C','Q','R','3','P')
GROUP BY small
";
        }

        protected string getProfitAndLostStatement()
        {
            return @"
SELECT
    --SUBSTRING('JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC ', (MONTH(YMD) * 4) - 3, 3) YM
    MONTH(YMD) YM
	,ACC
	,EXP9
	,AMOUNT
FROM (
    SELECT * FROM (
    SELECT	
        CASE WHEN B.ymd IS NULL THEN '1900-01-01'ELSE CONVERT(CHAR(12), B.ymd,111) END YMD
        ,'EXP9' ACC
	    ,A.九類 EXP9
	    ,sum(B.bal_2)AMOUNT
    FROM artm.dbo.GL_ACC_MAP_TABLE A LEFT OUTER JOIN
    (
	    SELECT
		    A.ymd
		    ,A.acc_no
		    ,A.big
		    ,bal_2
	    FROM artm.dbo.ARTM95_ABCDE A
	    WHERE A.ymd BETWEEN @ymd1 AND @ymd2
	    AND (A.ACC_NO LIKE '4%'or A.ACC_NO LIKE '5%')
	     {0}
    )B ON A.[會科] = B.acc_no
    GROUP BY B.YMD,A.[九類])A
    WHERE A.YMD > CAST('1911-01-01' AS DATE)

    UNION ALL

    SELECT 
	    ymd
        ,'ACC' ACC
	    ,'=""' + acc_no + '""' as  acc_no
	    ,SUM(bal_2) amount
    FROM ARTM95_ABCDE A
    WHERE A.ymd BETWEEN @ymd1 AND @ymd2
     {0}
     AND (A.acc_no LIKE '4251%'
    OR A.ACC_NO LIKE '4252%' OR A.ACC_NO LIKE '4253%' OR A.ACC_NO LIKE '4254%'
    OR A.ACC_NO LIKE '4255%' OR A.ACC_NO LIKE '4256%' OR A.ACC_NO LIKE '4257%'
    OR A.ACC_NO LIKE '4261%' OR A.ACC_NO LIKE '4262%' OR A.ACC_NO LIKE '4271%'
    OR A.ACC_NO LIKE '4301%' OR A.ACC_NO LIKE '4501%' OR A.ACC_NO LIKE '4516%'
    OR A.ACC_NO LIKE '4534%' OR A.ACC_NO LIKE '4611%' OR A.ACC_NO LIKE '4612%'
    OR A.ACC_NO LIKE '4613%' OR A.ACC_NO LIKE '4614%' OR A.ACC_NO LIKE '4615%'
    OR A.ACC_NO LIKE '4616%' OR A.ACC_NO LIKE '4618%' OR A.ACC_NO LIKE '4619%'
    OR A.ACC_NO LIKE '4701%' OR A.ACC_NO LIKE '4800%' OR A.ACC_NO LIKE '4801%'
    OR A.ACC_NO LIKE '4802%' OR A.ACC_NO LIKE '4805%' OR A.ACC_NO LIKE '4903%'
    OR A.ACC_NO LIKE '4911%' OR A.ACC_NO LIKE '4921%' OR A.ACC_NO LIKE '4923%'
    OR A.ACC_NO LIKE '4929%' OR A.ACC_NO LIKE '4960%' OR A.ACC_NO LIKE '5251%'
    OR A.ACC_NO LIKE '5252%' OR A.ACC_NO LIKE '5253%' OR A.ACC_NO LIKE '5254%'
    OR A.ACC_NO LIKE '5255%' OR A.ACC_NO LIKE '5256%' OR A.ACC_NO LIKE '5261%'
    OR A.ACC_NO LIKE '5271%' OR A.ACC_NO LIKE '5301%' OR A.ACC_NO LIKE '5501%'
    OR A.ACC_NO LIKE '5516%' OR A.ACC_NO LIKE '5533%' OR A.ACC_NO LIKE '5534%'
    OR A.ACC_NO LIKE '5535%' OR A.ACC_NO LIKE '5601%' OR A.ACC_NO LIKE '5602%'
    OR A.ACC_NO LIKE '5603%' OR A.ACC_NO LIKE '5604%' OR A.ACC_NO LIKE '5605%'
    OR A.ACC_NO LIKE '5606%' OR A.ACC_NO LIKE '5607%' OR A.ACC_NO LIKE '5608%'
    OR A.ACC_NO LIKE '5609%' OR A.ACC_NO LIKE '5701%' OR A.ACC_NO LIKE '5800%'
    OR A.ACC_NO LIKE '5801%' OR A.ACC_NO LIKE '5802%' OR A.ACC_NO LIKE '5805%'
    OR A.ACC_NO LIKE '5911%' OR A.ACC_NO LIKE '5913%' OR A.ACC_NO LIKE '5917%'
    OR A.ACC_NO LIKE '5918%' OR A.ACC_NO LIKE '5923%' OR A.ACC_NO LIKE '5929%')
    GROUP BY ymd,acc_no
)A
ORDER BY ACC,YM,EXP9
";
        }

        protected string getFeeStatement()
        {
            return @"
SELECT a.*,
	CASE 
		WHEN BIG = '彩券' THEN 1
		WHEN BIG = '信託' THEN 2
		WHEN BIG = '資訊' THEN 3
		WHEN BIG = '行管' THEN 4
		WHEN BIG = '財管' THEN 5
		WHEN BIG = '個金' THEN 6
		WHEN BIG = '企金' THEN 7
		WHEN BIG = '信卡' THEN 8
	END ORD
 FROM (
SELECT  
	CASE 
		WHEN BIG IN ('1','C','D','E','F','N') 
			OR (big='L' AND small ='9') 
			OR (big='8' AND small IN ('7','8') )
			THEN '企金'
		WHEN BIG IN('9','R','P') 
				OR (big='L' AND small ='8') 
				OR (big='8' AND small NOT IN('7','8'))
			THEN '行管'
		WHEN (BIG IN ('A') OR (BIG='7' AND small IN('7','6'))) THEN '財管'
		WHEN BIG IN ('B','K','M','J') THEN '個金'
		WHEN big = '2' THEN '彩券'
		WHEN big = '3' THEN '信託'
		WHEN big = 'H' THEN '信卡'
		WHEN (big = '7' AND small IN('1','2','3','4')) THEN '資訊'
	END	BIG
	,CASE
		WHEN (A.big not in ('3')) AND A.acc_no IN('4516016001'
					,'4516016002','4516016003','4516016004'
					,'4516016006','4516016008','4516016010','4516016014'
					,'4516036000','4516053000','4516066000','4516063000'
					,'4516076000','5516029000','5516035000','5516066000'
							)THEN 'A基金銷售'
		WHEN (A.big in ('2')) AND A.acc_no IN('4516038000','4516068000','5516020000','5516024000'
					,'5516068000') THEN 'B彩券業務'
		WHEN A.acc_no IN('4516042000','4516045000','4516047000','4516048000'
					,'4516049000','4516050000','4516051000','4516044000'
					,'4516064000','4516065000','4516073000') THEN 'C帳戶管理費'
		WHEN a.big= 'H' and A.acc_no IN('4516025000','4516026000','4516033000','4516060000'
					,'4516061000','4516084000','4516080001','5516009000'
					,'5516031000','5516060000','5516011000','5516022000'
					,'5516001000','4516080002','5516019000','5516025001'
					,'5516038000') THEN 'D信用卡業務'
		WHEN A.acc_no IN('4516013000','5516006000') THEN 'E保證業務'
		
		WHEN A.BIG= '3' AND A.acc_no IN('4516014002','4516021000','4516087000','5516007001'
					,'5516007000','4516019000','4516016006'  ) THEN 'F基金保管'   
		WHEN A.BIG <> '3' AND A.acc_no IN('4516014002','4516021000','4516087000','5516007001'
					 ) THEN 'F基金保管' 					 
					 
		WHEN (A.big not in ('H','3')) AND A.acc_no IN('4516001000','4516002000','4516004000','4516005000'
					,'4516006000','4516007000','4516009000','4516010000'
					,'4516011001','4516011002','4516011003','4516011005'
					,'4516011099','4516056000','5516001000','5516002000'
					,'5516003000','5516004000','5516018000','4516092000')
					THEN 'G外匯業務'					
		WHEN (A.big not in ('H','3')) AND A.acc_no IN('4516017000','4516018000','4516019000','4516020000'
				,'4516022000','4516039000','5516010000','5516011000'
				,'5516012000','5516016000','5516019000','5516027000'
				,'5516039000') THEN 'H代理業務'
		WHEN A.acc_no IN('4516023000','4516029000','4516052000','4516057000'
				,'4516058000') THEN 'I聯貸及參貸業務'
		WHEN (A.big not in ('3','H')) AND  A.acc_no IN(
				'4516003000','4516008000','4516012000','4516014001'
				,'4516014099','4516015000','4516024000','4516027000'
				,'4516028000','4516030000','4516031000','4516032000'
				,'4516034000','4516035000','4516037000','4516041000'
				,'4516054000','4516055000','4516098000','4516099000'
				,'4516043000','4516071000','4516059000','5516081000'
				,'4516067000','4516069000','4516072000','4516074000'
				,'4516077000','4516078000','4516080000','4516081000'
				,'4516082000','4516086000','4516090000','4516091000'
				,'5516005000','5516007002','5516008000','5516013000'
				,'5516014000','5516015000','5516017000','5516021000'
				,'5516022000','5516023000','5516025000','5516026000'
				,'5516028000','5516030000','5516055000','5516037000'
				,'5516040000','5516070000','5516099000','5516062000'
                ,'5516071000') THEN 'J其他'
				
		 WHEN A.big = '3' AND A.acc_no IN('4516016005','4516016007','4516016009','4516016011'
				,'4516016012','4516016013','4516040000','4516088000'
				,'5516099000','4516099000','4516098000','5516001000') THEN 'K信託業務'
				
		 WHEN A.big <>'3' AND A.acc_no IN('4516016005','4516016007','4516016009','4516016011'
				,'4516016012','4516016013','4516040000','4516088000'
				,'5516099000') THEN 'K信託業務'

		WHEN A.acc_no IN('4516075000') THEN 'L保險佣金'	
		ELSE 'NOTHING'
	END ACC
	,SUM(CASE WHEN acc_no LIKE '4%' THEN bal_2 ELSE 0 END) amount4
	,SUM(CASE WHEN acc_no LIKE '5%' THEN bal_2 ELSE 0 END) amount5
	,SUM(CASE WHEN acc_no LIKE '4%' THEN bal ELSE 0 END) BAL4
	,SUM(CASE WHEN acc_no LIKE '5%' THEN bal ELSE 0 END) BAL5
FROM ARTM95_ABCDE A
WHERE A.ymd = CAST(@ymd AS DATE)
--WHERE A.ymd = CAST('2013-08-31' AS DATE)
AND 
(A.big IN ('1','8','C','D','E','F','N','9','A','B','K','M','R','2','3','H','P')
	OR (A.big ='7' AND small IN('7','6'))
	OR (A.big ='L' AND small IN('9'))
	OR (A.big ='L' AND small IN('8'))
	OR (A.big ='7' AND small IN('1','2','3','4'))
    OR (A.big ='J' AND small IN('A'))
)
AND A.acc_no IN ('4516000000','4516001000','4516002000','4516003000'
,'4516004000','4516005000','4516006000','4516007000'
,'4516008000','4516009000','4516010000','4516011000'
,'4516011001','4516011002','4516011003','4516011005'
,'4516011099','4516012000','4516013000','4516014000'
,'4516014001','4516014002','4516014099','4516015000'
,'4516016000','4516016001','4516016002','4516016003'
,'4516016004','4516016005','4516016006','4516016007'
,'4516016008','4516016009','4516016010','4516016011'
,'4516016012','4516016013','4516016014','4516017000'
,'4516018000','4516019000','4516020000','4516021000'
,'4516022000','4516023000','4516024000','4516025000'
,'4516026000','4516027000','4516028000','4516029000'
,'4516030000','4516031000','4516032000','4516033000'
,'4516034000','4516035000','4516036000','4516036001'
,'4516036002','4516036003','4516036099','4516037000'
,'4516038000','4516039000','4516040000','4516040001'
,'4516040002','4516040003','4516040004','4516040005'
,'4516040009','4516040010','4516040011','4516040012'
,'4516040014','4516041000','4516042000','4516043000'
,'4516043001','4516043002','4516043003','4516043004'
,'4516044000','4516045000','4516045001','4516045002'
,'4516045003','4516045004','4516045005','4516045007'
,'4516045008','4516045009','4516045010','4516045011'
,'4516045012','4516045013','4516045014','4516045099'
,'4516047000','4516048000','4516049000','4516050000'
,'4516051000','4516052000','4516053000','4516053001'
,'4516053002','4516053003','4516054000','4516054001'
,'4516054002','4516054003','4516055000','4516056000'
,'4516057000','4516057001','4516057002','4516058000'
,'4516058001','4516058002','4516059000','4516060000'
,'4516061000','4516063000','4516063001','4516064000'
,'4516064001','4516064002','4516064003','4516065000'
,'4516065001','4516065002','4516065003','4516065004'
,'4516065005','4516065006','4516065007','4516066000'
,'4516066001','4516066002','4516066003','4516066004'
,'4516066005','4516066006','4516066007','4516066011'
,'4516066012','4516066013','4516066014','4516066015'
,'4516066021','4516066022','4516066023','4516066024'
,'4516066025','4516066026','4516067000','4516068000'
,'4516069000','4516069001','4516069002','4516071000'
,'4516072000','4516072001','4516072002','4516072003'
,'4516072004','4516072005','4516072006','4516072007'
,'4516072008','4516072011','4516072099','4516073000'
,'4516074000','4516075000','4516075001','4516075002'
,'4516075003','4516076000','4516076002','4516077000'
,'4516077001','4516078000','4516078001','4516078002'
,'4516080000','4516080001','4516080002','4516081000'
,'4516081001','4516082000','4516082001','4516084000'
,'4516084001','4516086000','4516087000','4516087001'
,'4516087002','4516087003','4516087004','4516087005'
,'4516087006','4516087007','4516088000','4516088002'
,'4516090000','4516090001','4516091000','4516091001'
,'4516098000','4516099000','5516000000','5516001000'
,'5516002000','5516003000','5516004000','5516005000'
,'5516006000','5516006001','5516006099','5516007000'
,'5516007001','5516007002','5516008000','5516009000'
,'5516010000','5516011000','5516012000','5516013000'
,'5516014000','5516014001','5516015000','5516016000'
,'5516017000','5516018000','5516019000','5516020000'
,'5516021000','5516021001','5516021002','5516021003'
,'5516021006','5516021007','5516022000','5516023000'
,'5516023001','5516023002','5516023003','5516024000'
,'5516025000','5516025001','5516025002','5516026000'
,'5516027000','5516028000','5516029000','5516030000'
,'5516031000','5516032000','5516033000','5516034000'
,'5516035000','5516037000','5516037001','5516037002'
,'5516038000','5516039000','5516039001','5516039002'
,'5516039003','5516039004','5516040000','5516055000'
,'5516060000','5516060005','5516062000','5516062001'
,'5516062003','5516066000','5516066001','5516066002'
,'5516066003','5516066004','5516068000','5516070000'
,'5516081000','5516081001','5516099000','4516092000'
,'5516071000'
)
GROUP BY
	CASE 
		WHEN BIG IN ('1','C','D','E','F','N') 
			OR (big='L' AND small ='9') 
			OR (big='8' AND small IN ('7','8') )
			THEN '企金'
		WHEN BIG IN('9','R','P') 
				OR (big='L' AND small ='8') 
				OR (big='8' AND small NOT IN('7','8'))
			THEN '行管'
		WHEN (BIG IN ('A') OR (BIG='7' AND small IN('7','6'))) THEN '財管'
		WHEN BIG IN ('B','K','M','J') THEN '個金'
		WHEN big = '2' THEN '彩券'
		WHEN big = '3' THEN '信託'
		WHEN big = 'H' THEN '信卡'
		WHEN (big = '7' AND small IN('1','2','3','4')) THEN '資訊'
	END	,
	 CASE
		WHEN (A.big not in ('3')) AND A.acc_no IN('4516016001'
					,'4516016002','4516016003','4516016004'
					,'4516016006','4516016008','4516016010','4516016014'
					,'4516036000','4516053000','4516066000','4516063000'
					,'4516076000','5516029000','5516035000','5516066000'
							)THEN 'A基金銷售'
		WHEN (A.big in ('2')) AND A.acc_no IN('4516038000','4516068000','5516020000','5516024000'
					,'5516068000') THEN 'B彩券業務'
		WHEN A.acc_no IN('4516042000','4516045000','4516047000','4516048000'
					,'4516049000','4516050000','4516051000','4516044000'
					,'4516064000','4516065000','4516073000') THEN 'C帳戶管理費'
		WHEN a.big= 'H' and A.acc_no IN('4516025000','4516026000','4516033000','4516060000'
					,'4516061000','4516084000','4516080001','5516009000'
					,'5516031000','5516060000','5516011000','5516022000'
					,'5516001000','4516080002','5516019000','5516025001'
					,'5516038000') THEN 'D信用卡業務'
		WHEN A.acc_no IN('4516013000','5516006000') THEN 'E保證業務'
		
		WHEN A.BIG= '3' AND A.acc_no IN('4516014002','4516021000','4516087000','5516007001'
					,'5516007000','4516019000','4516016006'  ) THEN 'F基金保管'   
		WHEN A.BIG <> '3' AND A.acc_no IN('4516014002','4516021000','4516087000','5516007001'
					 ) THEN 'F基金保管' 					 
					 
		WHEN (A.big not in ('H','3')) AND A.acc_no IN('4516001000','4516002000','4516004000','4516005000'
					,'4516006000','4516007000','4516009000','4516010000'
					,'4516011001','4516011002','4516011003','4516011005'
					,'4516011099','4516056000','5516001000','5516002000'
					,'5516003000','5516004000','5516018000','4516092000')
					THEN 'G外匯業務'					
		WHEN (A.big not in ('H','3')) AND A.acc_no IN('4516017000','4516018000','4516019000','4516020000'
				,'4516022000','4516039000','5516010000','5516011000'
				,'5516012000','5516016000','5516019000','5516027000'
				,'5516039000') THEN 'H代理業務'
		WHEN A.acc_no IN('4516023000','4516029000','4516052000','4516057000'
				,'4516058000') THEN 'I聯貸及參貸業務'
		--WHEN A.acc_no = '5516026000' THEN 'J其他'
		WHEN (A.big not in ('3','H')) AND  A.acc_no IN(
				'4516003000','4516008000','4516012000','4516014001'
				,'4516014099','4516015000','4516024000','4516027000'
				,'4516028000','4516030000','4516031000','4516032000'
				,'4516034000','4516035000','4516037000','4516041000'
				,'4516054000','4516055000','4516098000','4516099000'
				,'4516043000','4516071000','4516059000','5516081000'
				,'4516067000','4516069000','4516072000','4516074000'
				,'4516077000','4516078000','4516080000','4516081000'
				,'4516082000','4516086000','4516090000','4516091000'
				,'5516005000','5516007002','5516008000','5516013000'
				,'5516014000','5516015000','5516017000','5516021000'
				,'5516022000','5516023000','5516025000','5516026000'
				,'5516028000','5516030000','5516055000','5516037000'
				,'5516040000','5516070000','5516099000','5516062000'
                ,'5516071000') THEN 'J其他'
				
		WHEN A.big = '3' AND A.acc_no IN('4516016005','4516016007','4516016009','4516016011'
				,'4516016012','4516016013','4516040000','4516088000'
				,'5516099000','4516099000','4516098000','5516001000') THEN 'K信託業務'
		WHEN A.big <>'3' AND A.acc_no IN('4516016005','4516016007','4516016009','4516016011'
				,'4516016012','4516016013','4516040000','4516088000'
				,'5516099000') THEN 'K信託業務'
		WHEN A.acc_no IN('4516075000') THEN 'L保險佣金'	
		ELSE 'NOTHING'
	END
)A WHERE (A.ACC LIKE 'A%'
OR A.ACC LIKE 'B%'
OR A.ACC LIKE 'C%'
OR A.ACC LIKE 'D%'
OR A.ACC LIKE 'E%'
OR A.ACC LIKE 'F%'
OR A.ACC LIKE 'G%'
OR A.ACC LIKE 'H%'
OR A.ACC LIKE 'I%'
OR A.ACC LIKE 'J%'
OR A.ACC LIKE 'K%'
OR A.ACC LIKE 'L%')

ORDER BY 
	CASE 
		WHEN BIG = '彩券' THEN 10
		WHEN BIG = '信託' THEN 20
		WHEN BIG = '資訊' THEN 30
		WHEN BIG = '行管' THEN 40
		WHEN BIG = '財管' THEN 50
		WHEN BIG = '個金' THEN 60
		WHEN BIG = '企金' THEN 70
		WHEN BIG = '信卡' THEN 80
	END
	,ACC
";
        
        }

        protected string getFeeDataDetailStatement()
        {
            return @"

SELECT         
    '=""' + [branch] + '""' AS branch
    ,[cur]    
    ,'=""' + [big] + '""' AS big
    ,'=""' + small + '""' AS SMALL
    ,'=""' + [acc_no] + '""' AS ACC_NO
    ,[bal]
    ,[ymd]
    ,[bal_2]
FROM ARTM95_ABCDE A
WHERE A.ymd = CAST(@ymd AS DATE)
AND 
(A.big IN ('1','8','C','D','E','F','N','9','A','B','K','M','R','2','3','H','P')
	OR (A.big ='7' AND small IN('7','6'))
	OR (A.big ='L' AND small IN('9'))
	OR (A.big ='L' AND small IN('8'))
	OR (A.big ='7' AND small IN('1','2','3','4'))
    OR (A.big ='J' AND small IN('A'))
)
AND A.acc_no IN ('4516000000','4516001000','4516002000','4516003000'
,'4516004000','4516005000','4516006000','4516007000'
,'4516008000','4516009000','4516010000','4516011000'
,'4516011001','4516011002','4516011003','4516011005'
,'4516011099','4516012000','4516013000','4516014000'
,'4516014001','4516014002','4516014099','4516015000'
,'4516016000','4516016001','4516016002','4516016003'
,'4516016004','4516016005','4516016006','4516016007'
,'4516016008','4516016009','4516016010','4516016011'
,'4516016012','4516016013','4516016014','4516017000'
,'4516018000','4516019000','4516020000','4516021000'
,'4516022000','4516023000','4516024000','4516025000'
,'4516026000','4516027000','4516028000','4516029000'
,'4516030000','4516031000','4516032000','4516033000'
,'4516034000','4516035000','4516036000','4516036001'
,'4516036002','4516036003','4516036099','4516037000'
,'4516038000','4516039000','4516040000','4516040001'
,'4516040002','4516040003','4516040004','4516040005'
,'4516040009','4516040010','4516040011','4516040012'
,'4516040014','4516041000','4516042000','4516043000'
,'4516043001','4516043002','4516043003','4516043004'
,'4516044000','4516045000','4516045001','4516045002'
,'4516045003','4516045004','4516045005','4516045007'
,'4516045008','4516045009','4516045010','4516045011'
,'4516045012','4516045013','4516045014','4516045099'
,'4516047000','4516048000','4516049000','4516050000'
,'4516051000','4516052000','4516053000','4516053001'
,'4516053002','4516053003','4516054000','4516054001'
,'4516054002','4516054003','4516055000','4516056000'
,'4516057000','4516057001','4516057002','4516058000'
,'4516058001','4516058002','4516059000','4516060000'
,'4516061000','4516063000','4516063001','4516064000'
,'4516064001','4516064002','4516064003','4516065000'
,'4516065001','4516065002','4516065003','4516065004'
,'4516065005','4516065006','4516065007','4516066000'
,'4516066001','4516066002','4516066003','4516066004'
,'4516066005','4516066006','4516066007','4516066011'
,'4516066012','4516066013','4516066014','4516066015'
,'4516066021','4516066022','4516066023','4516066024'
,'4516066025','4516066026','4516067000','4516068000'
,'4516069000','4516069001','4516069002','4516071000'
,'4516072000','4516072001','4516072002','4516072003'
,'4516072004','4516072005','4516072006','4516072007'
,'4516072008','4516072011','4516072099','4516073000'
,'4516074000','4516075000','4516075001','4516075002'
,'4516075003','4516076000','4516076002','4516077000'
,'4516077001','4516078000','4516078001','4516078002'
,'4516080000','4516080001','4516080002','4516081000'
,'4516081001','4516082000','4516082001','4516084000'
,'4516084001','4516086000','4516087000','4516087001'
,'4516087002','4516087003','4516087004','4516087005'
,'4516087006','4516087007','4516088000','4516088002'
,'4516090000','4516090001','4516091000','4516091001'
,'4516098000','4516099000','5516000000','5516001000'
,'5516002000','5516003000','5516004000','5516005000'
,'5516006000','5516006001','5516006099','5516007000'
,'5516007001','5516007002','5516008000','5516009000'
,'5516010000','5516011000','5516012000','5516013000'
,'5516014000','5516014001','5516015000','5516016000'
,'5516017000','5516018000','5516019000','5516020000'
,'5516021000','5516021001','5516021002','5516021003'
,'5516021006','5516021007','5516022000','5516023000'
,'5516023001','5516023002','5516023003','5516024000'
,'5516025000','5516025001','5516025002','5516026000'
,'5516027000','5516028000','5516029000','5516030000'
,'5516031000','5516032000','5516033000','5516034000'
,'5516035000','5516037000','5516037001','5516037002'
,'5516038000','5516039000','5516039001','5516039002'
,'5516039003','5516039004','5516040000','5516055000'
,'5516060000','5516060005','5516062000','5516062001'
,'5516062003','5516066000','5516066001','5516066002'
,'5516066003','5516066004','5516068000','5516070000'
,'5516081000','5516081001','5516099000','4516092000'
,'4516043005','5516071000','4516091003'

)
";
        }

        protected DataTable getSmallData()
        {
            string[] smalls = { "Y"
,"X"
,"W"
,"V"
,"U"
,"T"
,"S"
,"R"
,"Q"
,"P"
,"O"
,"N"
,"M"
,"L"
,"K"
,"J"
,"I"
,"H"
,"G"
,"F"
,"E"
,"D"
,"C"
,"B"
,"A"
,"9"
,"8"
,"7"
,"6"
,"5"
,"4"
,"3"
,"2"
,"1"
,"0"
                            };
            return Utility.GetComplexlistboxTable("small", smalls, "small");
        }
        protected DataTable getBigData()
        {
            string[] bigs = {"R"
,"P"
,"N"
,"M"
,"L"
,"K"
,"J"
,"H"
,"G"
,"F"
,"E"
,"D"
,"C"
,"B"
,"A"
,"9"
,"8"
,"7"
,"3"
,"2"
,"1" };
            return Utility.GetComplexlistboxTable("big", bigs, "big");
        }
        protected DataTable getBranchData()
        {
            string[] branchs = { "963"
,"962"
,"960"
,"950"
,"910"
,"900"
,"892"
,"891"
,"890"
,"889"
,"888"
,"883"
,"882"
,"875"
,"873"
,"871"
,"870"
,"818"
,"810"
,"808"
,"754"
,"753"
,"752"
,"751"
,"750"
,"749"
,"748"
,"747"
,"746"
,"745"
,"743"
,"742"
,"741"
,"740"
,"739"
,"738"
,"737"
,"736"
,"735"
,"734"
,"733"
,"731"
,"730"
,"728"
,"727"
,"726"
,"725"
,"724"
,"723"
,"722"
,"721"
,"719"
,"718"
,"717"
,"716"
,"715"
,"713"
,"712"
,"711"
,"710"
,"709"
,"707"
,"706"
,"705"
,"704"
,"703"
,"702"
,"701"
,"689"
,"688"
,"687"
,"686"
,"685"
,"684"
,"682"
,"681"
,"680"
,"679"
,"678"
,"677"
,"676"
,"675"
,"674"
,"673"
,"672"
,"671"
,"670"
,"669"
,"668"
,"660"
,"650"
,"640"
,"630"
,"621"
,"620"
,"610"
,"600"
,"590"
,"580"
,"570"
,"563"
,"562"
,"561"
,"560"
,"550"
,"540"
,"530"
,"520"
,"510"
,"501"
,"500"
,"492"
,"491"
,"490"
,"480"
,"470"
,"462"
,"461"
,"460"
,"454"
,"450"
,"442"
,"441"
,"440"
,"431"
,"430"
,"422"
,"421"
,"420"
,"411"
,"410"
,"401"
,"400"
,"390"
,"380"
,"370"
,"361"
,"360"
,"350"
,"342"
,"341"
,"340"
,"330"
,"321"
,"320"
,"310"
,"307"
,"306"
,"305"
,"304"
,"303"
,"302"
,"301"
,"300"
,"260"
,"250"
,"232"
,"221"
,"220"
,"210"
,"21 "
,"209"
,"204"
,"203"
,"202"
,"200"
,"199"
,"191"
,"190"
,"052"
,"050"
,"021"
,"012"};
            return Utility.GetComplexlistboxTable("branch", branchs, "branch");
        }
        
    }


}
